package com.partsoft.dits.log;

import java.sql.Types;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Layout;
import org.apache.log4j.helpers.LogLog;
import org.apache.log4j.spi.LoggingEvent;

import com.partsoft.dits.utils.CalendarUtils;
import com.partsoft.dits.utils.DbUtils;
import com.partsoft.dits.utils.ExecutionCallback;
import com.partsoft.dits.utils.JdbcTemplate;
import com.partsoft.dits.utils.SQLTypeUtils;

import org.springframework.jdbc.core.support.SqlLobValue;
import org.springframework.transaction.interceptor.TransactionAttribute;
import org.springframework.util.Assert;


/**
 * 基本的数据源日志适配器
 */
public class BaseDataSourceLogAdapter extends Layout implements LogAdapter {
	
	private DataSource dataSource;
	
	private String namespace;
	
	private boolean autoDeploy = true;
	
	public BaseDataSourceLogAdapter() {}
	
	public BaseDataSourceLogAdapter(DataSource dataSource) {
		setDataSource(dataSource);
	}
	
	public BaseDataSourceLogAdapter(String namespace, DataSource dataSource) {
		setNamespace(namespace);
		setDataSource(dataSource);
	}
	
	public boolean isAutoDeploy() {
		return autoDeploy;
	}

	public void setAutoDeploy(boolean autoDeploy) {
		this.autoDeploy = autoDeploy;
	}

	public void activateOptions() {
		if (getDataSource() != null && isAutoDeploy() && !isDeployed()) {
			deployTables();
		}
	}

	@Override
	public String format(LoggingEvent loggingevent) {
		return "";
	}

	@Override
	public boolean ignoresThrowable() {
		return true;
	}

	public String getNamespace() {
		return namespace;
	}

	public void setNamespace(String namespace) {
		this.namespace = namespace;
	}

	public DataSource getDataSource() {
		return dataSource;
	}
	
	public void setDataSource(DataSource dataSource, boolean beDeploy) {
		this.dataSource = dataSource;
		if (this.getDataSource() != null && beDeploy && !isDeployed()) {
			deployTables();
		}
	}
	
	public void setDataSource(DataSource dataSource) {
		setDataSource(dataSource, false);
	}
	
	private void doSavelog(String idPrefix, DataSource ds, LogEntity logEntity) throws Exception {
		Assert.notNull(ds);
		Assert.notNull(logEntity);
		String logId = null;
		synchronized (BaseDataSourceLogAdapter.class) {
			String idned = CalendarUtils.toID(new Date());
			if (idPrefix != null) {
				if ((idPrefix.length() + idned.length()) > MAX_ID_SIZE) {
					throw new Exception(String.format("日志ID前缀不能大于%d个字符", MAX_ID_SIZE - idned.length()));
				}
				logId = String.format("%s%s", idPrefix, idned);
			} else {
				logId = idned;
			}
		}
		final String logSQL = "INSERT INTO DITS_LOGS(SID, SNAME, LOGLEVEL, LOGTYPE, DCLASSNAME, DCLASSID, CMESSAGE, FNAME, FLINE, CNAME, MNAME, TNAME, LOGDATE) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		final Object [] logParamerters = new Object[] {
			//SID
			SQLTypeUtils.toSqlParameterValue(logId, Types.VARCHAR),
			//SNAME
			SQLTypeUtils.toSqlParameterValue(idPrefix, Types.VARCHAR),
			//LOGLEVEL
			SQLTypeUtils.toSqlParameterValue(logEntity.getLevel() == null ? null : logEntity.getLevel().getCode(), Types.INTEGER),
			//LOGTYPE
			SQLTypeUtils.toSqlParameterValue(logEntity.getType() == null ? null : logEntity.getType().getCode(), Types.INTEGER), 
			//DCLASSNAME
			SQLTypeUtils.toSqlParameterValue(logEntity.getDescriptorClass() != null ? logEntity.getDescriptorClass().getName() : null, Types.VARCHAR),
			//DCLASSID
			SQLTypeUtils.toSqlParameterValue(logEntity.getDescriptorIdentity(), Types.VARCHAR),
			//CMESSAGE
			SQLTypeUtils.toSqlParameterValue(logEntity.getContent(), Types.VARCHAR),
			//FNAME
			SQLTypeUtils.toSqlParameterValue(logEntity.getFileName(), Types.VARCHAR),
			//FLINE
			SQLTypeUtils.toSqlParameterValue(logEntity.getLineNumber(), Types.VARCHAR),
			//CNAME
			SQLTypeUtils.toSqlParameterValue(logEntity.getClassName(), Types.VARCHAR),
			//MNAME
			SQLTypeUtils.toSqlParameterValue(logEntity.getMethodName(), Types.VARCHAR),
			//TNAME
			SQLTypeUtils.toSqlParameterValue(logEntity.getThreadName(), Types.VARCHAR),
			//LOGDATE
			SQLTypeUtils.toSqlParameterValue(logEntity.getLogDate(), Types.TIMESTAMP)
		};
		
		JdbcTemplate jdbcHelper = new JdbcTemplate(ds);
		jdbcHelper.update(logSQL, logParamerters);
		
		for (LogAttachment att : logEntity.getAttachments()) {
			if (att == null) return;
			final String attSQL = "INSERT INTO DITS_LOG_ATTS(LOGID, CNAME, OATT) VALUES(?, ?, ?)";
			final Object []attParamerters = new Object[] {
					//LOGID
					SQLTypeUtils.toSqlParameterValue(logId, Types.VARCHAR),
					//CNAME
					SQLTypeUtils.toSqlParameterValue(att.getName(), Types.VARCHAR),
					//OATT
					SQLTypeUtils.toSqlParameterValue(new SqlLobValue(att.getBytes()), Types.BLOB)
			};
			jdbcHelper.update(attSQL, attParamerters);
		}
	}

	public void savelog(final LogEntity logEntity) {
		Assert.notNull(getDataSource());
		Assert.notNull(logEntity);
		final DataSource ds = getDataSource();
		DbUtils.executeInTransaction(ds, TransactionAttribute.PROPAGATION_REQUIRES_NEW, new ExecutionCallback() {
			public void callback() {
				try {
					doSavelog(getNamespace(), ds, logEntity);
				} catch (Exception e) {
					LogLog.error(String.format("写入日志到数据库时失败:%s", e.getMessage()), e);
				}
			}
		});
	}
	
	public void deployTables() {
		try {
			LogLog.warn("准备初始化数据库日志表...");
			final String[] sqls = resolveDeploySQLScripts(getDataSource());
			if (sqls != null && sqls.length > 0) {
				DbUtils.executeInTransaction(getDataSource(), TransactionAttribute.PROPAGATION_REQUIRES_NEW, new ExecutionCallback() {
					public void callback() {
						doExecutionSQLs(getDataSource(), sqls);
					}
				});
			}
			LogLog.warn("初始化数据库日志表成功。");
		} catch (Exception e) {
			LogLog.error(String.format("初始化数据库日志失败:", e.getMessage()), e);
		}
	}
	
	protected void doExecutionSQLs(DataSource ds, String[]deploySQLs) {
		JdbcTemplate jdbcHelper = new JdbcTemplate(ds);
		jdbcHelper.batchUpdate(deploySQLs);
	}
	
	protected String[] resolveDeploySQLScripts(DataSource ds) {
		Assert.notNull(ds);
		String produceName = DbUtils.getDatabaseProduceName(ds);
		List<String> deploySQLs = new LinkedList<String>(); 
		if ( produceName.startsWith( "DB2" )) {
			deploySQLs.add("create table DITS_LOGS(SID VARCHAR(32) not null, SNAME VARCHAR(256), LOGLEVEL int, LOGTYPE int, DCLASSNAME VARCHAR(256), DCLASSID VARCHAR(256), CMESSAGE VARCHAR(256), FNAME VARCHAR(512), FLINE VARCHAR(32), TNAME VARCHAR(512), MNAME VARCHAR(128), CNAME VARCHAR(128), LOGDATE TIMESTAMP, constraint P_DITS_LOGS_PKEY primary key (SID))");
			deploySQLs.add("create table DITS_LOG_ATTS( SID BIGINT not null generated by default as identity, LOGID VARCHAR(32), CNAME VARCHAR(128) not null, OATT BLOB, constraint P_DITS_LOG_ATTS_PK primary key (SID))");
			deploySQLs.add("alter table DITS_LOG_ATTS add constraint F_ATTS_LOGID_REF foreign key (LOGID) references DITS_LOGS (SID) on delete restrict on update restrict");
		} else if (produceName.startsWith( "ORACLE" ) ) {
			deploySQLs.add("create table DITS_LOGS(SID VARCHAR2(32) not null, SNAME VARCHAR2(256), LOGLEVEL INTEGER, LOGTYPE INTEGER, DCLASSNAME VARCHAR2(256), DCLASSID VARCHAR2(256), FLINE VARCHAR2(32), CMESSAGE VARCHAR2(256), NAME VARCHAR2(512), TNAME VARCHAR2(512), MNAME VARCHAR2(128), CNAME VARCHAR2(128), LOGDATE TIMESTAMP, constraint PK_DITS_LOGS primary key (SID))");
			deploySQLs.add("create table DITS_LOG_ATTS(SID NUMBER(6) not null, LOGID VARCHAR2(32), CNAME VARCHAR2(128) not null, OATT BLOB, constraint PK_DITS_LOG_ATTS primary key (SID))");
			deploySQLs.add("alter table DITS_LOG_ATTS add constraint FK_DITS_LOG_ATTS_LOGI_DITS_LOG foreign key (LOGID) references DITS_LOGS (SID)");
			deploySQLs.add("create trigger tib_dits_log_atts before insert" + 
				"\non DITS_LOG_ATTS for each row" + 
				"\ndeclare" + 
				"\n    integrity_error  exception;" + 
				"\n    errno            integer;" + 
				"\n    errmsg           char(200);" + 
				"\n    dummy            integer;" + 
				"\n    found            boolean;" + 
				"\nbegin" + 
				"\n    select S_DITS_LOG_ATTS.NEXTVAL INTO :new.SID from dual;" + 
				"\nexception" + 
				"\n    when integrity_error then" + 
				"\n       raise_application_error(errno, errmsg);" + 
				"\nend;");
		} else if (produceName.startsWith( "MICROSOFT SQL SERVER")) {
			deploySQLs.add("create table DITS_LOGS (SID varchar(32) not null, SNAME varchar(256) null, LOGLEVEL int null, LOGTYPE int null,DCLASSNAME varchar(256) null, DCLASSID varchar(256) null, FLINE varchar(32) null, CMESSAGE varchar(256) null, FNAME varchar(512) null, TNAME varchar(512) null, MNAME varchar(128) null, CNAME varchar(128) null, LOGDATE     timestamp   null, constraint PK_DITS_LOGS primary key nonclustered (SID) )");
			deploySQLs.add("create table DITS_LOG_ATTS (SID bigint identity, LOGID varchar(32) null, CNAME varchar(128) not null, OATT BLOB null, constraint PK_DITS_LOG_ATTS primary key nonclustered (SID, SID))");
			deploySQLs.add("alter table DITS_LOG_ATTS add constraint FK_DITS_LOG_ATTS_LOGI_DITS_LOG foreign key (LOGID) references DITS_LOGS (SID)");
		} else {
			throw new IllegalArgumentException(String.format("未实现%s数据库的脚本。", produceName));
		}
		return deploySQLs.toArray(new String[0]);
	}
	
	public boolean isDeployed() {
		boolean deployed = false;
		try {
			deployed = DbUtils.hasTableName(getDataSource(), "DITS_LOGS");
		} catch (Throwable e) {}
		return deployed;
	}
	
	public void unDeployTables() {
		try {
			final String[] sqls = resolveUnDeploySQLScripts(getDataSource());
			if (sqls != null && sqls.length > 0) {
				DbUtils.executeInTransaction(getDataSource(), TransactionAttribute.PROPAGATION_REQUIRES_NEW, new ExecutionCallback() {
					public void callback() {
						doExecutionSQLs(getDataSource(), sqls);
					}
				});
			}
		} catch (Exception e) {
			LogLog.error(String.format("撤销数据库日志失败:%s", e.getMessage()), e);
		}
	}
	
	protected String[] resolveUnDeploySQLScripts(DataSource ds) {
		Assert.notNull(ds);
		String produceName = DbUtils.getDatabaseProduceName(ds);
		List<String> unDeploySQLs = new LinkedList<String>(); 
		if ( produceName.startsWith( "DB2" )) {
			unDeploySQLs.add("alter table DITS_LOG_ATTS drop foreign key F_ATTS_LOGID_REF");
			unDeploySQLs.add("drop table DITS_LOG_ATTS");
			unDeploySQLs.add("drop table DITS_LOGS");
		} else if (produceName.startsWith( "ORACLE" ) ) {
			unDeploySQLs.add("drop trigger tib_dits_log_atts");
			unDeploySQLs.add("alter table DITS_LOG_ATTS drop constraint FK_DITS_LOG_ATTS_LOGI_DITS_LOG");
			unDeploySQLs.add("drop table DITS_LOG_ATTS cascade constraints");
			unDeploySQLs.add("alter table DITS_LOG_ATTS drop constraint FK_DITS_LOG_ATTS_LOGI_DITS_LOG;");
		} else if (produceName.startsWith( "MICROSOFT SQL SERVER")) {
			unDeploySQLs.add("drop constraint FK_DITS_LOG_ATTS_LOGI_DITS_LOG");
			unDeploySQLs.add("drop table DITS_LOG_ATTS");
			unDeploySQLs.add("drop table DITS_LOGS");
		} else {
			throw new IllegalArgumentException(String.format("未实现%s数据库的脚本。", produceName));
		}
		return unDeploySQLs.toArray(new String[0]);
	}

}
